package DBLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.*;

public class DBLabour implements IFDBLabour
{
	 private  Connection connection;
	
	 public DBLabour() 
	    {
	      connection = DbConnection.getInstance().getDBcon();
	    }
	 
	 public ArrayList<Labour> getAllLabour() 
	 	{
	      return miscWhere("");
	    }
	 
	 private ArrayList<Labour> miscWhere(String wClause)
		{
			ArrayList<Labour> labours = new ArrayList<>();
			
			ResultSet results;
			String query = buildQuery(wClause);
			
			try{
				Statement stmt = connection.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);
				
				
				while(results.next())
				{
					Labour labour = buildLabour(results);
					labours.add(labour);
				}
				
				stmt.close();
				
			}
			catch(Exception ex){}
			return labours;
		}
	 
	 private String buildQuery(String wClause)
		{
		    String query="SELECT labourId, name FROM Labour";
			
			if (wClause.length()>0)
				query=query+" WHERE "+ wClause;
				
			return query;
		}
		
	 private Labour buildLabour(ResultSet results){
	        Labour labourObj = new Labour();
	          try{ 
	                labourObj.setLabourId(results.getInt("labourId"));
	                labourObj.setLabourName(results.getString("name"));    
	          }
	         catch(Exception e)
	         {
	             System.out.println("Cannot create labour object.");
	         }
	         return labourObj;
	      
	    }
	 
	 public int updateLabour(Labour labour)
		{
			Labour labourObj  = labour;
			int rc=-1;

			String query="UPDATE Labour SET "+
			 	  "labourId ="+ labourObj.getLabourID()+", "+
			 	  "name ='"+ labourObj.getLabourName() + "' " +
			      " WHERE labourId = "+ labourObj.getLabourID();
	                System.out.println("Update query:" + query);
	  		try{ 
		 		Statement stmt = connection.createStatement();
		 		stmt.setQueryTimeout(5);
		 	 	rc = stmt.executeUpdate(query);

		 	 	stmt.close();
			}
		 	catch(Exception ex){
		 	 	System.out.println("Update exception in labour db: "+ex);
		  	}
			return(rc);
		}
	 
	 public int deleteLabour(int labourId)
		{
	               int rc=-1;
		  
		  	String query="DELETE FROM Labour WHERE labourId = " +labourId;
	                System.out.println(query);
		  	try{ 
		 		Statement stmt = connection.createStatement();
		 		stmt.setQueryTimeout(5);
		 	  	rc = stmt.executeUpdate(query);
		 	  	stmt.close();
	  		}
	   	        catch(Exception ex){
		 	  	System.out.println("Delete exception in Labour db: "+ex);
	   	        }
			return(rc);
		}

	 public int insertLabour(Labour labour) throws Exception
	    {   
	       
	  
	       int rc = -1;
		   String query="INSERT INTO Labour(labourId, name) VALUES "
				+ "("+labour.getLabourID()+",'"+labour.getLabourName()+"')";
	                     

	       System.out.println("insert : " + query);
	      try{ 
	          Statement stmt = connection.createStatement();
	          stmt.setQueryTimeout(5);
	     	  rc = stmt.executeUpdate(query);
	          stmt.close();
	      }
	       catch(SQLException ex){
	          System.out.println("Labour insert Error");
	          throw new Exception ("Error");
	       }
	       return(rc);
	    }

	 public Labour findLabour(int labourId)
	    {   String wClause = "  labourId = " + labourId;
	        return singleWhere(wClause);
	    }
	 
	 private Labour singleWhere(String wClause)
		{
			Labour labour = new Labour();
			
			ResultSet results;
			String query = buildQuery(wClause);
			
			
			try{
				Statement stmt = connection.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);
				
				if(results.next())
					labour = buildLabour(results);
				
				stmt.close();
			}
			catch(Exception ex){}
			
			return labour;
		}
}
